-- HDS Consultores TI 
-- Hector R. De los Santos
-- hector@domipro.net
--
-- Stored Procedure Generator's | 2009 v0.2.1
-- Generador de Stored Procedures (Funciones) en PostgreSQL
-  Probado en: PostgreSQL 8.1.X
-- Caracteristicas:
-- 	Genera Stored Procedure para mantenimiento de tablas (altas y actualizaciones) en base a un esquema y tabla dada.
--
-- De donde Viene: Me base en: Pg-crud Generator (Stored Procedure Generator 0.2 )
--
-- Que le Agrege: Modifique la funcion que genera los updates para que valide el campo clave dado, si existe actualiza,
-- de lo contrario inserta ( genera un SP para Actualizar y otro para insertar ).
--
-- Como Funciona: Ejecutando Select utils.generate_storedprocedure(NombreEsquema,NombreTabla);
-- Esto generara dos Stored's Procedures:
-- 1) fn_NombreTabla(campos,campos,campos) _Este Store procedure es el principal, es desde donde se valida y se determina si se va a actualizar o insertar.
-- 2) ins_NombreTabla(campos,campos,campos) _Este es llamado desde el store procedure principal si se va a instertar.
--
-- Puntos a Considerar: Estos SP's toman en cuenta si el campo es primary key para validar los campos.
--
-- Licencia : BSD (haz lo que quieras con este codigo)
--
-- Como instalar: Crear esquema llamado utils y agregar estas funciones
-- Como usar: Select utils.generate_storedprocedure(NombreEsquema,NombreTabla);
-- Que le Disfrute!

CREATE OR REPLACE FUNCTION utils.getprimarykeyname (p_schemaname varchar, p_tablename varchar) RETURNS varchar AS
$body$
DECLARE
       v_keyColumnName varchar  ;
       v_ConstraintName varchar ;
BEGIN
     SELECT constraint_name
     FROM information_schema.table_constraints
     WHERE table_schema = p_schemaName AND table_name = p_tableName AND constraint_type='PRIMARY KEY'
     INTO v_ConstraintName ;
     /**
     * At this point we have the constraint name and we will look for the column that maps to the primary key contraint name
     */
     IF NOT FOUND THEN
        RAISE EXCEPTION 'Missing Primary Key for selected schema/table '; --||p_schemaName||'.'||p_tableName) ;
     ELSE
          SELECT column_name
          FROM information_schema.constraint_column_usage
          WHERE table_schema = p_schemaName AND table_name = p_tableName AND constraint_name = v_ConstraintName
          INTO v_keyColumnName ;
     END IF ;

     RETURN v_keyColumnName ;
END ;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION utils.getfieldnames (p_schemaname varchar, p_tablename varchar) RETURNS "pg_catalog"."refcursor" AS
$body$
DECLARE
	oFieldNames REFCURSOR;
BEGIN
	OPEN oFieldNames FOR
	SELECT column_name,data_type
	FROM
		information_schema.columns
	WHERE
		table_schema=p_schemaName and table_name = p_tableName
		;

	return oFieldNames ;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION "utils"."generateinsertstoredprocedure" (p_schemaname varchar, p_tablename varchar) RETURNS varchar AS
$body$
DECLARE
	oRecord 			RECORD;
	oTableDef			REFCURSOR;

	oFunctionBody		VARCHAR;
	oSQLCommand			VARCHAR;
	oParameterList		VARCHAR;
	oFieldNameList		VARCHAR;
	oParameterValues	VARCHAR;
	oPrimaryKeyName     VARCHAR;
	fnName              varchar;
	
BEGIN
    oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;
	oFunctionBody := 'CREATE OR REPLACE FUNCTION ' || p_schemaName || '.ins_' || p_tableName || '(';
	oSQLCommand :='INSERT INTO ' || p_schemaName || '.' || p_tableName || '(';
	OPEN oTableDef FOR SELECT utils.getFieldNames(p_schemaName,p_tableName);
	FETCH oTableDef INTO oTableDef;

	--FETCH oTableDef INTO oRecord;
	oParameterList := '';
	oFieldNameList :='' ;
	oParameterValues   :='' ;
LOOP
		FETCH oTableDef INTO oRecord ;
		-- Function body at this point should include Parameter list
		IF FOUND THEN
			/**
			* If we have a record Then we assign parameter list and continue building the SQL Command
			*
		 	* Inspecting if we must insert commas. The principal is as follows :
		 	*  If the parameter list is not empty i.e != '' then we have at least one element that is inserted and because the status is set to FOUND then it means the previous elements have to be appended with comma before adding the new fields found
		 	*  consider that the Parameter list is the same as the field name list that would be in the INSERT statement
			*/
			IF oParameterList != '' THEN
				oParameterList := oParameterList || ', ' ;
				oFieldNameList := oFieldNameList || ',' ;
				oParameterValues := oParameterValues || ',' ;
			
			END IF ;
			/**
			* Parameter list  and field name lists:
			* Not taking into account agn which is the primary key
            		*/
			IF oRecord.column_name != oPrimaryKeyName THEN
				oParameterValues := oParameterValues ||'p_'|| oRecord.column_name;
				oParameterList := oParameterList || 'p_' || oRecord.column_name || ' ' || oRecord.data_type ;
				oFieldNameList := oFieldNameList || oRecord.column_name ;
			END IF ;
		ELSE
			/**
            		* At this point nothing has been found in oRecord,
            		* We can now proceed to close the SQL Command with either parenthesis or semi colon
            		* We have to place the closing parenthesis and the Exit the loop
            		*/
			oParameterList := oParameterList || ')' ;
			oFieldNameList := oFieldNameList || ')' ;
			oParameterValues := oParameterValues ||') ; ';
			EXIT ;
		END IF ;
	
	END LOOP;
	-- Let's build the SQL Command string, remembering that the oParameterList and oFieldNameList already contains the closing parenthesis
	oSQLCommand := oSQLCommand || oFieldNameList ;
	oSQLCommand := oSQLCommand || ' VALUES (' || oParameterValues ;
	
	oFunctionBody := oFunctionBody || oParameterList;
	oFunctionBody := oFunctionBody || ' RETURNS VOID AS  ' || CHR(36) || CHR(36) || '  BEGIN ';

	oFunctionBody := oFunctionBody || oSQLCommand ;
	oFunctionBody := oFunctionBody || ' END; ' || CHR(36)||CHR(36) ||' LANGUAGE PLPGSQL ' ;

	CLOSE oTableDef ;
	-- INSERT INTO gQuery(query) VALUES (''||oFunctionBody||'') ;
	fnName := 'ins_' || p_tableName;
	EXECUTE ''|| oFunctionBody || '';
	RETURN fnName;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION utils.generate_storedprocedure (p_schemaname varchar, p_tablename varchar) RETURNS varchar AS
$body$
DECLARE
	oRecord		RECORD;
	oTableDef	REFCURSOR;

	oFunctionBody 	    VARCHAR;
	oSQLCommand	        VARCHAR;
	oFilter		        VARCHAR;
	oParameterList	    VARCHAR;
	oFieldNameList	    VARCHAR;
	oPrimaryKeyName     VARCHAR;
	oSqlChk             VARCHAR;
	oDespuesBd          VARCHAR;
	lNameRecord         varchar;
	lNamefun            varchar;
	recNameFun          record;
	SqltoIns            varchar;
	ListField           varchar;


	
BEGIN
     lNameRecord := 'registro';
     oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;
	-- Creating an update stored procedure for a given <p_schemaName>.<p_tableName>
	oFunctionBody 	:= 'CREATE OR REPLACE FUNCTION ' ||p_schemaName||'.fn_'||p_tableName ||'(' ;
	oSQLCommand 	:= chr(10)||'UPDATE '||p_schemaName ||'.'||p_tableName || chr(10)||' SET ';
	OPEN oTableDef FOR SELECT  utils.getFieldNames(p_schemaName,p_tableName);
	FETCH oTableDef INTO oTableDef;

	oParameterList 	:= '';
	oFieldNameList	:= '';
    ListField := '';
	LOOP
	    FETCH oTableDef INTO oRecord ;
		-- Function body at this point should include Parameter list
		IF FOUND THEN
			/**
			* If we have a record Then we assign parameter list and continue building the SQL Command
			*
		 	* Inspecting if we must insert commas. The principal is as follows :
		 	*  If the parameter list is not empty i.e != '' then we have at least one element that is inserted and because the status is set to FOUND then it means the previous elements have to be appended with comma before adding the new fields found
		 	*  consider that the Parameter list is the same as the field name list that would be in the INSERT statement
			*/


			IF oParameterList != '' THEN
				oParameterList := oParameterList || ', ' ;

				
			END IF ;
			
			IF oFieldNameList != '' THEN
				oFieldNameList := oFieldNameList || ',' ;
               
			END IF ;
			
		oParameterList := oParameterList || 'p_' || oRecord.column_name || ' ' || oRecord.data_type ;

		-- Parameter list  and field name lists:
		-- Not taking into account agn which is the primary key

			IF oRecord.column_name != oPrimaryKeyName THEN

            if ListField != '' THEN
			   ListField := ListField|| ',' ;
			 end if;			
			
               ListField := ListField || 'p_' ||oRecord.column_name;

				oFieldNameList := oFieldNameList|| oRecord.column_name ||' = '||'p_'|| oRecord.column_name ;



			ELSE
				-- Building the filter query WHERE agn = p_agn
				oFilter := ' WHERE '||oRecord.column_name||' = '||'p_'||oRecord.column_name ||';';
                oSqlChk := 'SELECT * from ' || p_tablename || ' INTO ' || lNameRecord || ' WHERE '||oRecord.column_name||' = '||'p_'||oRecord.column_name ||' for UPDATE'||';';

        	END IF ;
		ELSE
			-- We have to place the closing parenthesis and the Exit the loop
			oParameterList := oParameterList || ')' ;
		
			EXIT ;
		END IF ;


	END LOOP ;
        oSqlChk := oSqlChk || chr(10) || 'If found THEN';
	oDespuesBd := ' DECLARE ' || 'registro record;' || chr(10);
	
        oSQLCommand := oSqlChk || oSQLCommand || oFieldNameList ||oFilter ;
	oFunctionBody := oFunctionBody || oParameterList ;
	oFunctionBody := oFunctionBody || chr(10)||' RETURNS VOID AS  ' || CHR(36) || CHR(36) || oDespuesBd ||'  BEGIN ';
        Select utils.generateinsertstoredprocedure(p_schemaname,p_tablename) as nameFunc into recNameFun;
        lNamefun := trim(recNameFun.nameFunc);
        SqltoIns := chr(10) || 'perform ' || lNamefun||'('||ListField||');';
	oFunctionBody := oFunctionBody || oSQLCommand ;
	oFunctionBody := oFunctionBody || chr(10) || 'Else'|| SqltoIns || chr(10) || 'End if;' ||chr(10)|| ' END; ' || chr(10) || CHR(36)||CHR(36) ||' LANGUAGE PLPGSQL ' ;


	CLOSE oTableDef ;
	--INSERT INTO gquery(query) VALUES (oFunctionBody) ;
   EXECUTE ''||oFunctionBody ||'' ;
   --return oFunctionBody;
   return ListField;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;